EXISTS Condition
This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples.
Description
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the SQL EXISTS condition is:
WHERE EXISTS ( subquery );
Parameters or Arguments
subquery
The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.
Note
- SQL statements that use the EXISTS condition are very inefficient since the sub-query is rerun for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples for yourself!
Example - Using EXISTS With SELECT Statement
Let's look at an example that shows how to use the EXISTS condition with a SELECT statement.
In this example, we have a customers table with the following data:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
8000 Anderson Paige
9000 Johnson Derek www.techonthenet.com
And a table called orders with the following data:
ORDER_ID CUSTOMER_ID
-------- -----------
1 5000
2 6000
3 7000
4 9000
Now let's find all of the records from the customers table where there is at least one record in the orders table with the same customer_id. Enter the following SELECT statement:
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);
These are the results that you should see:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
9000 Johnson Derek www.techonthenet.com
In this example, there are 4 records in the customers where the customer_id value appears in the orders table.
Example - Using NOT EXISTS With SELECT Statement
The EXISTS condition can also be combined with the NOT operator to create a NOT EXISTS condition.
Let's use the NOT EXISTS condition to find all of the customers where there is not a record in the orders table with the same customer_id. Using the same customers and orders data as the previous example, enter the following SELECT statement:
SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);
These are the results that you should see:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
8000 Anderson Paige
In this example, there are 2 records in the customers table where the customer_id value does not appear in the orders table.
Example - Using EXISTS With INSERT Statement
Now, let's look at an example that shows how to use the EXISTS condition with an INSERT statement.
In this example, we have a contacts table that is empty and we want to populate:
CONTACT_ID LAST_NAME FIRST_NAME
---------- --------- ----------
Let's insert into the contacts table all of the customer_id, last_name, and first_name values from the customers table where there is at least one record in the orders table with a matching customer_id.
INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT customer_id, last_name, first_name
FROM customers
WHERE EXISTS (SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);
Then select the data from the contacts table:
SELECT * FROM contacts;
These are the results that you should see:
CONTACT_ID LAST_NAME FIRST_NAME
---------- --------- ----------
5000 Smith Jane
6000 Ferguson Samantha
7000 Reynolds Allen
9000 Johnson Derek
In this example, 4 records from the customers table will be inserted into the contacts table.
Example - Using EXISTS With UPDATE Statement
Let's look at an example that uses the EXISTS condition in an UPDATE statement.
In this example, we have a table called products with the following data:
PRODUCT_ID PRODUCT_NAME QUANTITY
---------- ------------ --------
1 Pear 25
2 Banana 0
3 Orange 18
4 Apple 45
And a table called summary_data with the following data:
PRODUCT_ID CURRENT_LEVELS
---------- --------------
1 10
2 10
3 10
4 10
5 10
Now let's update the summary_data table with values from the products table. Enter the following SQL statement:
UPDATE summary_data
SET current_levels = (SELECT quantity
FROM products
WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT quantity
FROM products
WHERE products.product_id = summary_data.product_id);
Then select the data from the summary_data table again:
SELECT * FROM summary_data;
These are the results that you should see:
PRODUCT_ID CURRENT_LEVELS
---------- --------------
1 25
2 0
3 18
4 45
5 10
In this example, 4 records would be updated in the summary_data table.
TIP: Notice that our UPDATE statement included an EXISTS condition in the WHERE clause to make sure that there was a matching product_id in both the products and summary_data table before updating the record.
If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_levels field to NULL in the last row of the summary_data table (because the products table does not have a record where product_id is 5).
Example - Using EXISTS With DELETE Statement
Let's look at an example that uses the EXISTS condition in a DELETE statement.
In this example, we have a table called products with the following data:
PRODUCT_ID PRODUCT_NAME QUANTITY
---------- ------------ --------
1 Pear 25
2 Banana 0
3 Orange 18
4 Apple 45
And a table called archival with the following data:
PRODUCT_ID
----------
1
2
3
4
5
Now let's delete records from the archival table where there is at least one record in the products table with a matching product_id. Enter the following SQL statement:
DELETE FROM archival
WHERE EXISTS (SELECT *
FROM products
WHERE products.product_id = archival.product_id);
Then select the data from the archival table again:
SELECT * FROM archival;
These are the results that you should see:
PRODUCT_ID
----------
5
In this example, 4 records from the archival table will be deleted, leaving just one record remaining.